package www.lagou.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import www.lagou.entity.phone;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class phoneDao {

    /**
     * 需求一获取价格高于2000的手机
     */

    public List<phone> findAllPhone(double price) throws SQLException {

        //创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //SQL语句
        String sql = "select * from phone where price > ?";

        //价格高于2000的手机的信息集合
        List<phone> phoneList = qr.query(sql,new BeanListHandler<phone>(phone.class),price);

        List<phone> list = new ArrayList<>();
        //遍历集合查询符合的phone
        for(phone p1:phoneList){
            int id = p1.getId();
            phone phone = findPhoneByDate(id);

            list.add(phone);
        }

        return list;
    }

    /**
     * 需求二
     */

    public phone findPhoneByDate(int id) throws SQLException {
        //创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //sql语句
        String sql = "select * from phone where prodate < '2019-01-01' and id = ?";

        phone p = qr.query(sql, new BeanHandler<phone>(phone.class), id);

        return p;
    }

    /**
     * 需求三 查询手机颜色是白色的所有手机信息
     */

    public List<phone> findPhoneByColor(String color) throws SQLException {
        //创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //sql语句
        String sql = "select * from phone where color = ?";

        List<phone> phoneList = qr.query(sql, new BeanListHandler<phone>(phone.class), color);

        return phoneList;


    }
}
